# Third-party wheels
import sqlite3

# My wheels
import PackagedInfo
import PublicConstant
import Settings


def to_safe_string(s: str):
    return '"' + s.replace('"', '\\"') + '"'


def from_safe_string(s: str):
    return s[1:-1].replace('\\"', '"')


def create_table_by_keyword(search_keyword: str):
    connection = sqlite3.connect(PublicConstant.database_file)
    try:
        sql_command = f'create table "{search_keyword}" (' \
                      f'  id               BIGINT  PRIMARY KEY,' \
                      f'  like_num         INTEGER NOT NULL,' \
                      f'  is_r18           BOOLEAN NOT NULL,' \
                      f'  is_ai            BOOLEAN NOT NULL,' \
                      f'  thumbnail_url    STRING  NOT NULL,' \
                      f'  update_timestamp BIGINT  NOT NULL' \
                      f')'
        cursor = connection.cursor()
        cursor.execute(sql_command)
        cursor.close()
        connection.commit()
        print(f'Table "{search_keyword}" has been created successfully.')
    except sqlite3.OperationalError as e:
        print(type(e), 'occurred in DatabaseOperation.create_table_by_keyword:', e)
    connection.close()


def fetch_data_by_keyword(
        search_keyword: str, settings: Settings.Settings) -> tuple[PackagedInfo.PackagedPicInfo] | None:
    sql_command = f'select * from {to_safe_string(search_keyword)} where like_num>=?'
    if not settings.allow_r18:
        sql_command += ' AND is_r18==0'
    if not settings.allow_normal:
        sql_command += ' AND is_r18==1'
    if not settings.allow_ai:
        sql_command += ' AND is_ai==0'
    sql_command += ' order by like_num desc'
    connection = sqlite3.connect(PublicConstant.database_file)
    try:
        cursor = connection.cursor()
        result = cursor.execute(sql_command, (settings.like_num_threshold,))
        answer = tuple([
            PackagedInfo.PackagedPicInfo(
                _id=item[0], _like_num=item[1], _is_r18=(item[2] == 1),
                _is_ai=(item[3] == 1), _thumbnail_url=item[4], _upload_date=item[5]
            ) for item in result
        ])
        cursor.close()
        connection.close()
        return answer
    except sqlite3.OperationalError as e:
        print(type(e), 'occurred in DatabaseOperation.fetch_data_by_keyword:', e)
    connection.close()


def insert_data_by_keyword(
        search_keyword: str, pic_list: list[PackagedInfo.PackagedPicInfo]):
    sql_command_insert = f'insert into {to_safe_string(search_keyword)} values (?, ?, ?, ?, ?, ?)'
    sql_command_update = f'update {to_safe_string(search_keyword)} set like_num = ? where id = ?'
    connection = sqlite3.connect(PublicConstant.database_file)
    cursor = connection.cursor()
    for item in pic_list:
        if item.like_num < PublicConstant.database_like_num_threshold:
            continue
        try:
            cursor.execute(
                sql_command_insert,
                (item.id, item.like_num, item.is_r18, item.is_ai, item.thumbnail_url, item.upload_date)
            )
        except sqlite3.IntegrityError:
            cursor.execute(sql_command_update, (item.like_num, item.id))
    cursor.close()
    connection.commit()
    connection.close()
